package com.gugu.procedure.dao;

import com.gugu.procedure.bean.ProductionRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface ProductionRecordDao extends JpaRepository <ProductionRecord, Long>, JpaSpecificationExecutor <ProductionRecord> {


    List <ProductionRecord> findAllByOrderNoAndSpecsAndProductionAndStatusAndIsDelOrderByNumber(String orderNo, String specs, String production, String status, String isDel);


    @Query(value = "select ng_tag, specs s, count(1) num ,(select  amount  from order_item where  order_no=?1 and specs=s limit 0,1) total   from  production_record  where " +
            "   order_no=?1   and production=?2 " +
            "  and status='1' and  is_del='0' and  end_time is not null GROUP BY specs , ng_tag", nativeQuery = true)
    List <Map <String, Object>> subNum(String orderNo, String production);

    @Query(value = "select  DISTINCT number   from  production_record  where  order_no=?1  and specs=?2 and if(?3!='',staff_name=?3,1=1) and   production=?4 and status='1' and  is_del='0'    and  end_time is not null  " +
            " order by id  limit 0,?5", nativeQuery = true)
    List <String> queryBackNumber(String orderNo, String specs, String staffName, String production, Integer num);


    //修理品订单工艺列表查询
    @Query(value = "select staff_name ,specs,count(1) num ,?2 production ,?1 orderNo  from   production_record  where  order_no=?1 and production=?2  " +
            " and status='1' and  is_del='0' and  end_time is not null    group BY staff_name ,specs", nativeQuery = true)
    List <Map <String, Object>> queryListForUpdate(String orderNo, String production);

    @Query(value = "  select  number  from  production_record  where  order_no=?1 and production=?2 and status='1' and  is_del='0'", nativeQuery = true)
    List <String> queryNumbers(String orderNo, String production);


    // 查询某个订单工艺的总规格数量
    @Query(value = "select sum(amount) from order_item where  order_no=?1  and  specs in(" +
            "  select specs  from  production where  order_no=?1 and procedures like ?2 ) ", nativeQuery = true)
    int querySum(String orderNo, String production);

    // 修理订单工艺流转的时候 需要判断您当前工艺是否合格
    @Query(value = "select  *  from  production_record  where  order_no=?1 and specs=?2 and status in(1,2)  order by  number  desc", nativeQuery = true)
    List <ProductionRecord> countByOrderNoAndSpecsOrderByNumberDesc(String orderNo, String specs);


    // 修理订单工艺流转的时候 需要判断您当前工艺是否合格
    @Query(value = "select  *  from  production_record  where  order_no=?1 and specs=?2 and if(?4!='',ng_tag=?4,1=1)  and production=?3 and status in(1,2)  order by  convert(number,SIGNED) desc  ", nativeQuery = true)
    List <ProductionRecord> countByOrderNoAndSpecsAndProductionOrderByNumberDesc(String orderNo, String specs, String production, String ngTag);

    @Query(value = "select  *  from  production_record  where  order_no=?1 and specs=?2 and ng_tag=?4 and  end_time is not null and production=?3 and status in(1,2)  order by  convert(number,SIGNED)  desc", nativeQuery = true)
    List <ProductionRecord> countByOrderNoAndSpecsAndProductionAndEndTimeNotNullOrderByNumberDesc(String orderNo, String specs, String production, String ngTag);

    // 修理订单工艺流转的时候 需要判断您当前工艺是否合格
    @Query(value = "select  *  from  production_record  where  order_no=?1 and specs=?2  and ng_tag=?4 and production=?3 and status in(1,2) and end_time is  null  order by   convert(number,SIGNED) desc", nativeQuery = true)
    List <ProductionRecord> queryByOrderNoAndSpecsAndProductionOrderByNumberAsc(String orderNo, String specs, String production, String ngTag);


    // 查询某个产品最新的流转工艺
    ProductionRecord findTopByOrderNoAndSpecsAndStatusOrderByCreateTimeDesc(String orderNo, String specs, String status);

    //  注意这个修改
    ProductionRecord findTopByOrderNoAndNumberAndStatusOrderByIdDesc(String orderNo, String number, String status);

    //将createTime 改为Id
    @Query(value = "select  *  from  production_record  where  order_no=?1 and number=?2    and production=?3 and status in(1,2)  order by id desc limit 0,1", nativeQuery = true)
    ProductionRecord findTopByOrderNoAndNumberAndProductionOrderByIdDesc(String orderNo, String number, String production);

    //将createTime 改为Id
    @Query(value = "select  *  from  production_record  where  order_no=?1 and number=?2   and production=?3 and status in(1,2)  order by id desc limit 0,1", nativeQuery = true)
    ProductionRecord findTopByOrderNoAndNumberAndProductionAndNgTagOrderByIdDesc(String orderNo, String number, String production);

    @Query(value = "select  *  from  production_record  where  order_no=?1 and number=?2   and production=?3 and status in(1,2)  order by create_time desc limit 0,1", nativeQuery = true)
    ProductionRecord findOrderNoAndNumberAndProduction(String orderNo, String number, String production);


    @Query(value = "select  *  from  production_record  where  order_no=?1  and status in(1,2,5) order by id desc ", nativeQuery = true)
    List <ProductionRecord> findByOrderNo(String orderNo);

    //将createTime 改为Id
    ProductionRecord findTopByOrderNoAndNumberAndProductionAndStatusOrderByIdDesc(String orderNo, String number, String production, String status);

    // 查询订单某个工艺完成了多少个
    @Query(value = "select  count(1) from production_record where order_no=?1 and production=?2 and status=?3 and  end_time is not null", nativeQuery = true)
    int countProductionRecord(String orderNo, String production, String status);


    // 查询订单下某个工艺的产品信息
    List <ProductionRecord> findByOrderNoAndProductionAndStatus(String orderNo, String production, String status);


    List <ProductionRecord> findByOrderNoAndNumberAndStatusAndProductionIn(String orderNo, String number, String status, List <String> productions);


    List <ProductionRecord> findByOrderNoAndNumberAndStatusIn(String orderNo, String number, List <String> status);


    @Query(value = "select  *  from  (select order_no,specs,staff_id,production, date_format(start_date, '%Y-%m-%d') startDate,count(1) amount ,'0' status\n" +
            "from   production_record  \n" +
            "where end_time is  null   and  status in('1','0') and if (?1!='',order_no=?1,1=1) and if(?2!='',staff_number=?2,1=1)" +
            "and  if(?3!='',start_time between  ?3 and ?4,1=1) and if(?5!='',production=?5,1=1)\n" +
            "GROUP BY order_no,specs,staff_id ,production,start_date" +
            " UNION " +
            "select order_no,specs,staff_id,production,date_format(start_date, '%Y-%m-%d') startDate,count(1) amount ,'1' status\n" +
            "from   production_record  \n" +
            "where end_time is not   null  and  status in('1','0') and if (?1!='',order_no=?1,1=1) and if(?2!='',staff_number=?2,1=1)" +
            "and  if(?3!='',start_date between  ?3 and ?4,1=1) and if(?5!='',production=?5,1=1)\n" +
            "GROUP BY order_no,specs,staff_id ,production,start_date)  a where 1=1 and if(?6!='',a.status=?6,1=1) limit ?7,?8", nativeQuery = true)
    List <Map <String, Object>> listStaffProductionRecord(String orderNo, String staffNumber, String startTime, String endTime, String production, String status, int pageNo, int pageSize);

    @Query(value = "select  count(1)  from  (select order_no,specs,staff_id,production,count(1) amount ,'0' status\n" +
            "from   production_record  \n" +
            "where end_time is  null   and  status in('1','0') and  if (?1!='',order_no=?1,1=1) and if(?2!='',staff_number=?2,1=1)" +
            "and  if(?3!='',start_time between  ?3 and ?4,1=1) and if(?5!='',production=?5,1=1)\n" +
            "GROUP BY order_no,specs,staff_id ,production,start_date" +
            " UNION " +
            "select order_no,specs,staff_id,production,count(1) amount ,'1' status\n" +
            "from   production_record  \n" +
            "where end_time is not   null   and  status in('1','0') and if (?1!='',order_no=?1,1=1) and if(?2!='',staff_number=?2,1=1)" +
            "and  if(?3!='',start_date between  ?3 and ?4,1=1) and if(?5!='',production=?5,1=1)\n" +
            "GROUP BY order_no,specs,staff_id ,production,start_date)  a where 1=1 and if(?6!='',a.status=?6,1=1) ", nativeQuery = true)
    long countStaffProductionRecord(String orderNo, String staffNumber, String startTime, String endTime, String production, String status);


    @Query(value = "select min(start_time) start_time ,max(end_time) end_time from production_record where order_no=?1 and specs=?2 and production=?3 and status=1  and end_time is not  null ", nativeQuery = true)
    Map <String, Object> queryProductionRecordStartTimeAndEndTime(String orderNo, String specs, String production);


    @Query(value = "select min(start_time) start_time   from production_record where order_no=?1 and specs=?2 and production=?3 and status=1  and end_time is   null", nativeQuery = true)
    Map <String, Object> queryProductionRecordStartTime(String orderNo, String specs, String production);


    @Query(value = "select *  from  production_record where order_no=?1 and  specs=?2 and production=?3 and staff_number=?4 and  work_done_flag='0'  and status='1'", nativeQuery = true)
    List <ProductionRecord> listWorkDoneProductionRecord(String orderNo, String specs, String production, String staffNumber);

    List <ProductionRecord> findByWorkDoneId(Long workDoneId);

    @Query(value = "select  DISTINCT  production from production_record  where production is not null", nativeQuery = true)
    List <String> listProduction();

    void deleteAllByOrderNo(String orderNo);


    ProductionRecord findTopByOrderNoAndNumberAndProductionAndStatusAndEndTimeNotNull(String orderNo, String number, String production, String status);


}
